﻿using Microsoft.AspNetCore.Mvc;

namespace WebApplicationKG.Controllers
{
    public class Dept        //部门类
    {
        #region 两个构造函数
        public Dept()
        {; }
        public Dept(int deptId, string deptName)
        {
            this.DeptId = deptId;
            this.DeptName = deptName;
        }
        #endregion

        public int DeptId { get; set; } = 0;//部门编号
        public string DeptName { get; set; } = "";//部门名称
    }

    public class Employee
    {
        public Employee()
        {; }
        public Employee(int empId, int deptId, string empName, string empPhone, double empSalary)
        {
            this.EmpId = empId;
            this.DeptId = deptId;
            this.EmpName = empName;
            this.EmpPhone = empPhone; ;
            this.EmpSalary = empSalary;
        }
        public int EmpId { get; set; } = 0;//员工编号
        public int DeptId { get; set; } = 0;//部门编号
        public string EmpName { get; set; } = "";//员工姓名
        public string EmpPhone { get; set; } = "";//员工电话
        public double EmpSalary { get; set; } = 0;//员工工资
    }

    public class Chp02Controller : Controller
    {
        #region 添加了两个函数
        public List<Dept> LoadDept()//添加部门
        {
            List<Dept> list = new List<Dept>();
            list.Add(new Dept(1, "开发部"));
            list.Add(new Dept(2, "测试部"));
            list.Add(new Dept(3, "实施部"));
            return list;
        }
        public List<Employee> LoadEmployee()
        {
            List<Employee> list = new List<Employee>();
            list.Add(new Employee(1, 1, "刘备", "13587457458", 6000));
            list.Add(new Employee(2, 2, "关羽", "13698547412", 8000));
            list.Add(new Employee(3, 3, "张飞", "13245785412", 9000));
            list.Add(new Employee(4, 1, "赵云", "13666669856", 3000));
            list.Add(new Employee(5, 2, "马超", "13224245785", 7000));
            list.Add(new Employee(6, 3, "魏延", "15325254545", 4000));
            list.Add(new Employee(7, 1, "周仓", "13855566624", 2000));
            list.Add(new Employee(8, 2, "廖化", "13999955558", 5000));
            list.Add(new Employee(9, 3, "曹操", "13002025458", 9000));
            return list;
        }
        #endregion
        #region Demo01 Linq和Lambda筛选数组！
        public IActionResult Demo01()
        {
            int[] arr = new int[10] { 55, 85, 35, 69, 74, 12, 85, 47, 95, 32 };
            //使用Linq筛选数组中50-80的数据
            var linqRes = from v in arr
                          where v >= 50 && v < 80
                          select v;
            ViewBag.linqRes = linqRes;

            //使用Lambda筛选数组中50-80的数据
            var lambdaRes = arr.Where(p => (p >= 50 && p <= 80));
            ViewBag.lambdaRes = lambdaRes;


            return View();
        }
        #endregion

        #region Demo02 查询工资在5000及以上，电话以132开头的员工信息
        public IActionResult Demo02()
        {
            List<Employee> list = new List<Employee>();
            list = LoadEmployee();
            //使用linq查询工资在5000及以上，电话以132开头的员工信息
            var linqRes = from emp in list
                          where emp.EmpSalary >= 5000 && emp.EmpPhone.StartsWith("132")
                          select emp;
            ViewBag.linqRes = linqRes;


            //使用lambda查询工资在5000及以上，电话以132开头的员工信息
            var lambdaRes = list.Where(p => p.EmpSalary >= 5000 && p.EmpPhone.StartsWith("132"));
            ViewBag.lambdaRes = lambdaRes;

            return View();
        }
        #endregion

        #region  Demo03 先安照工资降序，后按照编号的降序进行排列
        public IActionResult Demo03()
        {
            List<Employee> list = new List<Employee>();//创造数据
            list = LoadEmployee();//加载数据
            //linq(ascending:正序；descending:倒序)
            var listLinq = from emp in list
                           orderby emp.EmpSalary descending, emp.EmpId descending
                           select emp;
            ViewBag.listLinq = listLinq;

            //lambda(OrderBy:正序；OrderByDescending:倒序；第二次排序使用ThenBy)
            var listLambda = list.OrderByDescending(p => p.EmpSalary).ThenByDescending(p => p.EmpId);
            ViewBag.listLambda = listLambda;
            return View();
        }
        #endregion

        #region  Demo04 使用linq和lambda进行分组
        public IActionResult Demo04()
        {
            //按照部门编号分组显示
            List<Employee> list = new List<Employee>();//创造数据
            list = LoadEmployee();//加载数据

            //linq方式
            var listLinq = from emp in list
                           group emp by emp.DeptId;
            ViewBag.listLinq = listLinq;
            //lambda方式
            var listLambda = list.GroupBy(p => p.DeptId);
            ViewBag.listLambda = listLambda;

            return View();
        }
        #endregion

        #region 求实发工资的函数
        public double ComputeSfgz(double salary)
        {
            double 税金 = (salary - 1200 - 3500) * 0.03;//计算税金
            double 实发工资 = salary - 1200 - (税金 > 0 ? 税金 : 0);
            return 实发工资;
        }
        #endregion

        #region  Demo05 使用linq和lambda进行分组加聚合条件
        public IActionResult Demo05()
        {
            //按照部门编号分组显示（只查询平均工资在5000及以上的组）
            List<Employee> list = new List<Employee>();//创造数据
            list = LoadEmployee();//加载数据

            //linq方式
            var listLinq = from emp in list
                           group emp by emp.DeptId
                           into g
                           where g.Average(p => p.EmpSalary) >= 5000
                           select g;
            ViewBag.listLinq = listLinq;
            //lambda方式
            var listLambda = list.GroupBy(p => p.DeptId).Where(p => p.Average(avg => avg.EmpSalary) >= 5000);
            ViewBag.listLambda = listLambda;

            return View();
        }
        #endregion

        #region  Demo06 匿名类集合的显示
        public IActionResult Demo06()
        {
            
            List<Employee> list = new List<Employee>();//创造数据
            list = LoadEmployee();//加载数据

            //linq方式
            var listRes = from emp in list
                          let sj = (emp.EmpSalary - 1200 - 3500) * 0.03
                          let sfgz = emp.EmpSalary - 1200 - (sj > 0 ? sj : 0)
                          select new
                          {
                              EmpId = emp.EmpId,
                              DeptId = emp.DeptId,
                              EmpName = emp.EmpName,
                              EmpPhone = emp.EmpPhone,
                              EmpSalary = emp.EmpSalary,
                              EmpSFGZ = sfgz
                          };
            ViewBag.listRes = listRes;
            //lambda方式


            return View();
        }
        #endregion

        #region  Demo07 联表查询
        public IActionResult Demo07()
        {
            
            List<Dept> listDept = new List<Dept>();//创造数据
            listDept = LoadDept();//加载数据
            List<Employee> listEmp= new List<Employee>();
            listEmp= LoadEmployee();
            //Linq语法
            //var listView = from emp in listEmp
            //             join dept in listDept on emp.DeptId equals dept.DeptId
            //              select new
            //              {
            //                  EmpId = emp.EmpId,
            //                  DeptId = emp.DeptId,
            //                  DeptName=dept.DeptName,
            //                  EmpName = emp.EmpName,
            //                  EmpPhone = emp.EmpPhone,
            //                  EmpSalary = emp.EmpSalary
            //              };
            //ViewBag.listView = listView;

            //lambda方式
            var listView = listEmp.Join(listDept, emp => emp.DeptId, dept => dept.DeptId, (emp, dept) => new
            {
                EmpId = emp.EmpId,
                DeptId = emp.DeptId,
                DeptName = dept.DeptName,
                EmpName = emp.EmpName,
                EmpPhone = emp.EmpPhone,
                EmpSalary = emp.EmpSalary
            });

            ViewBag.listView = listView;
            return View();
        }
        #endregion

        #region  Demo08 分组加聚合
        public IActionResult Demo08()
        {
            List<Dept> listDept=new List<Dept>();
            listDept = LoadDept();

            List<Employee> listEmp = new List<Employee>();
            listEmp = LoadEmployee();

            var listView = from emp in listEmp
                           join dept in listDept on emp.DeptId equals dept.DeptId
                           group emp by new { dept.DeptId, dept.DeptName } into g  //假设是g
                           select new
                           {
                               部门编号 = g.Key.DeptId,
                               部门名称 = g.Key.DeptName,
                               部门人数 = g.Count(),
                               工资总和 = g.Sum(p => p.EmpSalary),
                               平均工资 = g.Average(p => p.EmpSalary),
                               最高工资 = g.Max(p => p.EmpSalary),
                               最低工资 = g.Min(p => p.EmpSalary),
                           };
            ViewBag.listView = listView;

            return View();
        }

        #endregion
    }
}
